﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DataHelper;
using Utils;
namespace DAL
{
    public class MiniDao
    {
        public bool Insert(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().InsertTable(tableName, sdList);
        }
        public bool Update(string tableName, SortedDictionary<string, string> sdList)
        {
            return new BuiltSQL().UpdateTable(tableName, sdList);
        }
        public string Delete(string tableName, string[] primaryKey)
        {
            return new BuiltSQL().DeleteTable(tableName, primaryKey);
        }
        #region   Hostel
        public bool DeleteHostel(string hostelCode)
        {
            return SQLHelper.ExecuteSqlWithTransaction("Delete from Mini_Hostel Where hostelCode='" + hostelCode + "'");
        }
        //获取一条记录
        public DataTable GetOneHostel(string hostelCode)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from Mini_Hostel Where  hostelCode='" + hostelCode + "'");
        }
        //列表SQL
        public string GetHostelSQL(string serWord, string bTime, string eTime, string statu)
        {
            if (statu == "") statu = "Y";
            StringBuilder strSQL = new StringBuilder("Select * From Mini_Hostel Where Statu ='" + statu + "' ");
            if (!string.IsNullOrWhiteSpace(serWord) && serWord != "null")
                strSQL.Append(" And (HostelCode='" + serWord + "' Or Addr Like '%" + serWord + "%')");
            else
            {

                if (RegexRule.IsDate(bTime))
                    strSQL.Append(" And Etime>='" + bTime + "'");
                if (RegexRule.IsDate(eTime))
                    strSQL.Append(" And Etime<='" + eTime + "'");
            }
            return strSQL.ToString();
        }
        public DataTable GetHostelLists()
        {
            return SQLHelper.ExecuteSqlGetDataSet("select HostelCode as ID,HostelCode+' '+Addr as [Name] from Mini_Hostel where Statu='Y' order by ID asc").Tables[0];
        }
        //列表
        public DataTable GetHostelLists(string serWord, string bTime, string eTime, string statu)
        {
            return SQLHelper.ExecuteSqlGetDrToDt(GetHostelSQL(serWord, bTime, eTime, statu));
        }


        //入住people
        public bool DeleteHostelEmp(string id)
        {
            return SQLHelper.ExecuteSqlWithTransaction("Delete from Mini_HostelEmp Where ID=" + id);
        }
        //获取一条记录
        public DataTable GetOneHostelEmp(string id)
        {           
                return SQLHelper.ExecuteSqlGetDrToDt("Select * from Mini_HostelEmp Where  ID=" + id);
        }
        public DataTable GetOneHostelEmp(string empCode, string statu)
        {
            if (statu == "")
                return SQLHelper.ExecuteSqlGetDrToDt("Select * from Mini_HostelEmp Where  EmpCode='" + empCode + "'");
            else
                return SQLHelper.ExecuteSqlGetDrToDt("Select * from Mini_HostelEmp Where Statu='" + statu + "' and EmpCode='" + empCode + "'");
        }
        //列表SQL
        public string GetHostelEmpSQL(string serWord, string hostelCode, string statu)
        {
            if (statu == "") statu = "Y";
            StringBuilder strSQL = new StringBuilder("Select * From Mini_HostelEmp Where Statu ='" + statu + "' ");
            if (!string.IsNullOrWhiteSpace(serWord) && serWord != "null")
                strSQL.Append(" And (EmpName Like '%" + serWord + "%' Or EmpCode Like '%" + serWord + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(hostelCode) && hostelCode != "0")
                    strSQL.Append(" And HostelCode = '" + hostelCode + "' ");
            }
            strSQL.Append("  order by HostelCode asc,DeptName asc ");
            return strSQL.ToString();
        }
        //列表
        public DataTable GetHostelEmpLists(string serWord, string hostelCode, string statu)
        {
            return SQLHelper.ExecuteSqlGetDrToDt(GetHostelEmpSQL(serWord, hostelCode, statu));
        }
        public DataTable GetEmpByHostel(string hostelCode, string statu)
        {
            if (statu == "") statu = "Y";
            return SQLHelper.ExecuteSqlGetDrToDt("Select * From Mini_HostelEmp Where Statu ='" + statu + "' and HostelCode ='" + hostelCode + "' order by deptname asc");
        }
        //获取某个员工的交费记录
        public DataTable GetEmpFei(string strYear,string strMonth)
        {
            if (strYear=="")
                return SQLHelper.ExecuteSqlGetDrToDt("Select a.*,b.EmpName from Mini_HostelEmpFee a,Mini_HostelEmp b  where a.EmpCode=b.EmpCode and b.Statu='Y' order by a.FYear DESC,a.FMonth ASC");
            else
                return SQLHelper.ExecuteSqlGetDrToDt("Select a.*,b.EmpName from Mini_HostelEmpFee a,Mini_HostelEmp b  where a.EmpCode=b.EmpCode and b.Statu='Y' and  a.FYear=" + strYear + " and  a.FMonth=" + strMonth + " order by a.ID ASC");
        }

        public DataTable GetEmpFei(string empCode,string strYear, string strMonth)
        {
            if (empCode != "")
                return SQLHelper.ExecuteSqlGetDrToDt("Select a.*,b.EmpName from Mini_HostelEmpFee a,Mini_HostelEmp b  where a.EmpCode=b.EmpCode and (b.EmpName like '%" + empCode + "%' or a.EmpCode like '%" + empCode + "%'  ) order by a.FYear DESC,a.FMonth ASC");
            else
                return SQLHelper.ExecuteSqlGetDrToDt("Select a.*,b.EmpName from Mini_HostelEmpFee a,Mini_HostelEmp b  where a.EmpCode=b.EmpCode and  a.FYear=" + strYear + " and  a.FMonth=" + strMonth + " and (b.EmpName like '%" + empCode + "%' or a.EmpCode like '%" + empCode + "%'  ) order by a.FYear DESC,a.FMonth ASC");
        }
        //判断本月住宿费是否已经交
        public bool IsExistFei(string empCode,string strYear,string strMonth)
        { 
            DataTable dt=SQLHelper.ExecuteSqlGetDrToDt("Select * from Mini_HostelEmpFee Where EmpCode='"+empCode+"' and FYear="+strYear+" and FMonth="+strMonth );
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;

        }
        //判断该工号有没有住宿
        public bool IsInHostel(string empCode)
        {
            if (GetOneHostelEmp(empCode, "Y").Rows.Count > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region  car

        #endregion

        #region   zj
        public bool DeleteCred(string id)
        {
            return SQLHelper.ExecuteSqlWithTransaction("Delete from Mini_Credential Where ID='" + id + "'");
        }
        //获取一条记录
        public DataTable GetOneCred(string id)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("select *  from View_Credential Where ID='" + id + "'");
        }

        //列表
        public DataTable GetCredLists(string serWord)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("select *  from View_Credential Where Statu='Y' and CredName like '%" + serWord + "%' order by Etime ASC,NextTime ASC");

        }
        public string GetCredNum(int dayNum, int getType)
        {
            if (getType == 0)
                return SQLHelper.ExecuteSqlGetNumber(" Select count(*) from Mini_Credential where Statu='Y' and datediff(day,getdate(),Etime)<=" + dayNum);
            else
                return SQLHelper.ExecuteSqlGetNumber(" Select count(*) from Mini_Credential where Statu='Y' and datediff(day,getdate(),NextTime)<=" + dayNum);
        }
        #endregion
    }
}
